package com.gemptc.daoimp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.gemptc.bean.Collectbean;
import com.gemptc.bean.Sciencebean;
import com.gemptc.dao.ISelectScience;
import com.gemptc.utils.DBUtil;
import com.gemptc.utils.DataUtil;

public class SelectScienceImp implements ISelectScience {

	// 查找游记科普表的内容
	@Override
	public List<Sciencebean> getSciencebean(int pageNow) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int m = (pageNow - 1) * DataUtil.pageNum;
		int n = DataUtil.pageNum;
		List<Sciencebean> sciencebeanlList = new ArrayList<Sciencebean>();
		String sql = "select* from science where s_type=1  limit ?,?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, m);
			preparedStatement.setInt(2, n);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int s_id1 = resultSet.getInt(1);
				String s_title = resultSet.getString(2);
				String s_content = resultSet.getString(3);
				String s_upload = resultSet.getString(4);
				int s_type = resultSet.getInt(5);
				String s_photo_path = resultSet.getString(6);
				String s_photo_jianjie = resultSet.getString(7);
				Sciencebean sciencebean = new Sciencebean(s_id1, s_title,
						s_content, s_upload, s_type, s_photo_path,
						s_photo_jianjie);
				sciencebeanlList.add(sciencebean);
				System.out.println(sciencebeanlList);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return sciencebeanlList;
	}

	// 查找游记推荐表的内容
	@Override
	public List<Sciencebean> getSciencebeanKePu(int pageNow) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int m = (pageNow - 1) * DataUtil.pageNum;
		int n = DataUtil.pageNum;
		List<Sciencebean> sciencebeanlList = new ArrayList<Sciencebean>();
		String sql = "select* from science where s_type=2 limit ?,?";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, m);
			preparedStatement.setInt(2, n);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int s_id1 = resultSet.getInt(1);
				String s_title = resultSet.getString(2);
				String s_content = resultSet.getString(3);
				String s_upload = resultSet.getString(4);
				int s_type = resultSet.getInt(5);
				String s_photo_path = resultSet.getString(6);
				String s_photo_jianjie = resultSet.getString(7);
				Sciencebean sciencebean = new Sciencebean(s_id1, s_title,
						s_content, s_upload, s_type, s_photo_path,
						s_photo_jianjie);
				sciencebeanlList.add(sciencebean);
				System.out.println(sciencebeanlList);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return sciencebeanlList;
	}

	// 收藏科普推荐
	@Override
	public boolean Collect(int user_id, int s_id, int c) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = null;
		switch (c) {
		case 1:
			sql = "INSERT INTO collect (user_id,s_id) VALUES (?,?);";
			break;
		// 删除科普推荐
		case 2:
			sql = "DELETE FROM collect WHERE user_id=? AND s_id= ?;";
			break;
		case 3:
			sql = "SELECT c_id FROM collect WHERE user_id=? AND s_id=? ;";
			break;
		}

		boolean flag = false;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, user_id);
			preparedStatement.setInt(2, s_id);
			if (c ==1 || c==2) {
				preparedStatement.execute();
				flag = true;
			} else {
				resultSet = preparedStatement.executeQuery();
				while (resultSet.next()) {
					flag = true;
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return flag;
	}

	// 删除科普推荐

	// 查看收藏的具体内容
	@Override
	public List<Collectbean> Collect(int user_id) {
		Connection connection = DBUtil.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		List<Collectbean> list = new ArrayList<Collectbean>();
		String sql = "SELECT s_id FROM collect WHERE user_id=?;";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, user_id);
			resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				int s_id = resultSet.getInt(1);
				Collectbean collectbean = new Collectbean(s_id);
				list.add(collectbean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.release(resultSet, preparedStatement, connection);
		}
		return list;
	}

}
